package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import model.Book;
import util.StringUtil;

/**
 * 图书Ddao类
 * @author 梁
 *
 */
public class BookDao {
	/**
	 * 图书添加
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public int add(Connection con,Book book)throws Exception{
		String sql="insert into t_book values(null,?,?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setInt(5, book.getBookTypeId());
		pstmt.setString(6 ,book.getBookDesc());
		pstmt.setString(7 ,book.getStatus());
		return pstmt.executeUpdate();
		
	}
	/**
	 * 图书信息查询
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public ResultSet list(Connection con,Book book)throws Exception{
		StringBuffer stb=new StringBuffer("select * from t_book b,t_bookType bt where b.bookTypeId=bt.id");
		if(StringUtil.isNotEmpty(book.getBookName())) {
			stb.append(" and b.bookName like '%"+book.getBookName()+"%'");
		}
		if(StringUtil.isNotEmpty(book.getAuthor())) {
			stb.append(" and b.author like '%"+book.getAuthor()+"%'");
		}
		if(book.getBookTypeId()!=null && book.getBookTypeId()!=-1) {
			stb.append(" and b.bookTypeId="+book.getBookTypeId());
		}
		if(StringUtil.isNotEmpty(book.getStatus())) {
			stb.append(" and b.status like '%"+book.getStatus()+"%'");
		}
		
		PreparedStatement pstmt=con.prepareStatement(stb.toString());
		return pstmt.executeQuery();
	}
	
	/**
	 * 图书信息查询
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public ResultSet list2(Connection con,Book book)throws Exception{
		StringBuffer stb=new StringBuffer("select * from t_book b,t_bookType bt where b.bookTypeId=bt.id ");
		if(StringUtil.isNotEmpty(book.getBookName())) {
			stb.append(" and b.bookName like '%"+book.getBookName()+"%'");
		}
		if(StringUtil.isNotEmpty(String.valueOf(book.getId()))) {
			stb.append(" and b.id like '%"+book.getId()+"%'");
		}

		PreparedStatement pstmt=con.prepareStatement(stb.toString());
		return pstmt.executeQuery();//返回结果集ResultSet
	}
	
	
	/**
	 * 图书信息删除
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int delete(Connection con,String id) throws Exception {
		String sql="delete from t_book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, id);
		return pstmt.executeUpdate();
	}
	
	public int delete2(Connection con,String bookTypeId) throws Exception {
		String sql="delete from t_book where bookTypeId=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, bookTypeId);
		return pstmt.executeUpdate();
	}
	
	/**
	 * 图书信息修改
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int update(Connection con,Book book) throws Exception{
		String sql="update t_book set bookName =?,author=?,sex=?,price=?,bookDesc=?,bookTypeId=?,status=?where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setString(5, book.getBookDesc());
		pstmt.setInt(6, book.getBookTypeId());
		pstmt.setString(7, book.getStatus());
		pstmt.setInt(8, book.getId());
		return pstmt.executeUpdate();
		
	}
	
	/**
	 * 图书借阅后信息更新
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int update2(Connection con,Book book) throws Exception{
		String sql="update t_book set status=?where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);		
		pstmt.setString(1, book.getStatus());
		pstmt.setInt(2, book.getId());
		return pstmt.executeUpdate();
		
	}
	
	
}
